﻿Imports System.Data
Imports System.Data.SqlClient

Public Class GiangVienDAO
    Private Shared lenhInsert As SqlCommand
    Private Shared lenhUpdate As SqlCommand
    Private Shared lenhDelete As SqlCommand
    Private dataProvider As DataProvider

    Public Sub New()
        dataProvider = New DataProvider
    End Sub
    'Public Shared Function GetDataGV() As DataTable
    '    Dim dt As DataTable = dataProvider.Executequery("sp_XuatGV")
    '    Return dt
    'End Function
    Public Shared Function GetData() As DataTable
        Dim dt As DataTable = dataProvider.Executequery("sp_XuatGV")
        Return dt
    End Function
   
    Public Shared Sub ThemGV(ByVal gv As GiangVienDTO)
        Dim sql As String

        sql = "INSERT INTO GIANGVIEN(MaGiangVien,HoTen,SoDienThoai,GioiTinh,NgaySinh,CMDN,DiaChi) VALUES(@MaGiangVien,@HoTen,@SoDienThoai,@GioiTinh,@NgaySinh,@CMDN,@DiaChi)"
        lenhInsert = New SqlCommand()
        lenhInsert.CommandText = sql
        Dim thamSo As SqlParameter

        thamSo = New SqlParameter("@MaGiangVien", SqlDbType.VarChar)
        thamSo.SourceColumn = gv.MaGiangVien
        lenhInsert.Parameters.Add(thamSo)

        thamSo = New SqlParameter("@HoTen", SqlDbType.NVarChar)
        thamSo.SourceColumn = gv.HoTen
        lenhInsert.Parameters.Add(thamSo)

        thamSo = New SqlParameter("@GioiTinh", SqlDbType.NVarChar)
        thamSo.SourceColumn = gv.GioiTinh
        lenhInsert.Parameters.Add(thamSo)

        thamSo = New SqlParameter("@NgaySinh", SqlDbType.DateTime)
        thamSo.SourceColumn = gv.NgaySinh
        lenhInsert.Parameters.Add(thamSo)

        thamSo = New SqlParameter("@SoDienThoai", SqlDbType.VarChar)
        thamSo.SourceColumn = gv.SoDienThoai
        lenhInsert.Parameters.Add(thamSo)

        thamSo = New SqlParameter("@CMND", SqlDbType.Char)
        thamSo.SourceColumn = gv.CMND
        lenhInsert.Parameters.Add(thamSo)

        thamSo = New SqlParameter("@DiaChi", SqlDbType.NVarChar)
        thamSo.SourceColumn = gv.DiaChi
        lenhInsert.Parameters.Add(thamSo)

    End Sub
    Public Shared Sub UpDateGV(ByVal gv As GiangVienDTO)
        Dim sql As String

        sql = "UPDATE GIANGVIEN SET HoTen=@HoTen,SoDienThoai=@SoDienThoai,GioiTinh=@GioiTinh,NgaySinh=@NgaySinh,CMND=@CMND,DiaChi=@DiaChi WHERE MaGiangVien=@MaGiangVien"
        lenhUpdate = New SqlCommand()
        lenhUpdate.CommandText = sql
        Dim thamSo As SqlParameter

        thamSo = New SqlParameter("@MaGiangVien", SqlDbType.VarChar)
        thamSo.SourceColumn = gv.MaGiangVien
        lenhUpdate.Parameters.Add(thamSo)

        thamSo = New SqlParameter("@HoTen", SqlDbType.NVarChar)
        thamSo.SourceColumn = gv.HoTen
        lenhUpdate.Parameters.Add(thamSo)

        thamSo = New SqlParameter("@GioiTinh", SqlDbType.NVarChar)
        thamSo.SourceColumn = gv.GioiTinh
        lenhUpdate.Parameters.Add(thamSo)

        thamSo = New SqlParameter("@NgaySinh", SqlDbType.DateTime)
        thamSo.SourceColumn = gv.NgaySinh
        lenhUpdate.Parameters.Add(thamSo)

        thamSo = New SqlParameter("@SoDienThoai", SqlDbType.VarChar)
        thamSo.SourceColumn = gv.SoDienThoai
        lenhUpdate.Parameters.Add(thamSo)

        thamSo = New SqlParameter("@CMND", SqlDbType.Char)
        thamSo.SourceColumn = gv.CMND
        lenhUpdate.Parameters.Add(thamSo)

        thamSo = New SqlParameter("@DiaChi", SqlDbType.NVarChar)
        thamSo.SourceColumn = gv.DiaChi
        lenhUpdate.Parameters.Add(thamSo)
    End Sub
    Public Shared Sub DeleteGV(ByVal qlgvDTO As GiangVienDTO)
        Dim sql As String
        sql = "DELETE FROM  GIANGVIEN WHERE MaGiangVien=@MaGiangVien"
        lenhDelete = New SqlCommand()
        lenhDelete.CommandText = sql
        Dim thamSo As SqlParameter
        thamSo = New SqlParameter("@MaGiangVien", SqlDbType.VarChar)
        thamSo.SourceColumn = "MaGiangVien"
        lenhDelete.Parameters.Add(thamSo)
    End Sub
    Public Shared Sub CapNhat(ByVal bangGiangVien As DataTable)
        'dataProvider.GhiBang(bangGiangVien, "GIANGVIEN", lenhInsert, lenhUpdate, lenhDelete)
        dataProvider.GhiBang(bangGiangVien, "GIANGVIEN")
    End Sub

    'Public Shared Function LayDSGiangVien() As DataTable
    '    Dim bangGiangVien As DataTable = dataProvider.DocCauTruc("SELECT MaGiangVien,HoTen,GioiTinh,NgaySinh,SoDienThoai,CMND,DiaChi FROM GIANGVIEN")
    '    Return bangGiangVien
    '    Dim bangGiangVien As DataTable = dataProvider.DocCauTruc("SELECT * FROM GIANGVIEN")
    '    Return bangGiangVien
    'End Function
    Public Shared Function LayDSGiangVien() As DataTable
        Return dataProvider.DocBang("SELECT MaGiangVien,HoTen,GioiTinh,NgaySinh,SoDienThoai,CMND,DiaChi FROM GIANGVIEN")
    End Function
    Public Shared Function LayCauTrucBang() As DataTable
        Return dataProvider.DocCauTruc("SELECT * FROM GIANGVIEN")
    End Function

End Class
